import models.connect_db as db
import models.city_value as cityv
import models.city_main as citym
from flask import request,  jsonify
import datetime
from dateutil.relativedelta import relativedelta
import pandas as pd
from scipy.stats import pearsonr
from sklearn.preprocessing import MinMaxScaler
import math

def send_map_data():
    """
    所有城市的所有指标
    :return:
    """
    mes = request.get_json()

    type = mes['type']
    date = mes['date']
    main = mes['main']
    if (type == 'week'):
        sql_str = "select * from city_week where date= '"+date+"'"
        sql1 = "select * from pollution_iaqi_week where date= '"+date+"'"
        sql = "select city_name,category from cluster_week where date= '"+date+"' and main_index='"+main+"'"
    if (type == 'month'):
        sql_str = "select * from city_month where date= '"+date+"'"
        sql1 = "select * from pollution_iaqi_month where date= '" + date + "'"
        sql = "select city_name,category from cluster_month where date= '" + date + "' and main_index='" + main + "'"
    if (type == 'quarter'):
        sql_str = "select * from city_quarter where date= '"+date[0]+"'"
        sql1 = "select * from pollution_iaqi_quarter where date= '" + date[0] + "'"
        sql = "select city_name,category from cluster_quarter where date= '" + date[0] + "' and main_index='" + main + "'"
    req = db.get_conn(sql_str)
    req1 = db.get_conn(sql)
    req2 = db.get_conn(sql1)
    dict_a = {}
    dict_b = {}
    dict_c = {}
    for item in req:
        dict_a.update({item[0]: [item[7], item[8],item[9], item[10],item[11],item[12],item[13], item[14],item[15],item[16]]})
    for item in req1:
        dict_b.update({item[0]: item[1]})
    for item in req2:
        dict_c.update({item[1]:[item[1],item[2],item[3], item[4], item[5],item[6],item[7]]})
        dict_c.update({})
    result_dict = {}
    for k, v in dict_c.items():
        for m, n in dict_a.items():
            for x, y in dict_b.items():
                if k == m and k == x:
                    result_dict[k] = dict_c[k]
                    for item in dict_a[k]:
                        result_dict[k].append(item)
                    result_dict[k].append(dict_b[k])
    list1 = list(result_dict.values())

    keys = ['city_name','PM2.5(微克每立方米)', 'PM10(微克每立方米)', 'SO2(微克每立方米)', 'NO2(微克每立方米)', 'CO(毫克每立方米)', 'O3(微克每立方米)', 'RH(%)',
                'PSFC(Pa)', 'AQI', '风速', '风向', '摄氏温度','date','id','lon','lat','category']
    data = []
    for item in list1:
        data.append(dict(zip(keys, item)))

    return jsonify(data)

def send_citys_indexs():
    """
    所有城市的所有指标
    :return:
    """
    mes = request.get_json()

    type = mes['type']
    date = mes['date']
    main = mes['main']
    if (type == 'week'):
        sql_str = "select * from city_week where date= '"+date+"'"
        sql = "select city_name,category from cluster_week where date= '"+date+"' and main_index='"+main+"'"
    if (type == 'month'):
        sql_str = "select * from city_month where date= '"+date+"'"
        sql = "select city_name,category from cluster_month where date= '" + date + "' and main_index='" + main + "'"
    if (type == 'quarter'):
        sql_str = "select * from city_quarter where date= '"+date[0]+"'"
        sql = "select city_name,category from cluster_quarter where date= '" + date[0] + "' and main_index='" + main + "'"
    req = db.get_conn(sql_str)
    req1 = db.get_conn(sql)
    dict_a = {}
    dict_b = {}
    for item in req:
        dict_a.update({item[0]: [item[0], item[1], item[2],item[3], item[4], item[5],item[6], item[7], item[8],item[9], item[10],item[11],item[12],item[13], item[14],item[15],item[16]]})
    for item in req1:
        dict_b.update({item[0]: item[1]})
    result_dict = {}
    for k, v in dict_a.items():
        for m, n in dict_b.items():
            if k == m:
                result_dict[k] = dict_a[k]
                result_dict[k].append(dict_b[k])
                dict_a[k] = result_dict[k]
                dict_b[k] = result_dict[k]
            else:
                result_dict[k] = dict_a[k]
                result_dict[m] = dict_b[m]
    list1 = list(result_dict.values())

    keys = ['city_name','PM2.5(微克每立方米)', 'PM10(微克每立方米)', 'SO2(微克每立方米)', 'NO2(微克每立方米)', 'CO(毫克每立方米)', 'O3(微克每立方米)', 'RH(%)',
                'PSFC(Pa)', 'AQI', '风速', '风向', '摄氏温度','date','id','lon','lat','category']
    data = []
    for item in list1:
        data.append(dict(zip(keys, item)))
    return jsonify(data)

def send_citys_mainIndex():
    """
    所有城市的主要指标前20和增长率
    :return:
    """
    mes = request.get_json()
    type = mes['type']
    date = mes['date']
    main = mes['main']
    if date == '2013-01-01' or date == '2013-01' or date[0] == '2013-01':
        if (type == 'week'):
            sql_str = "select city_name,`" + main + "` from city_week where date= '" + date + "'"
            sql = "select city_name,category from cluster_week where date= '" + date + "' and main_index='" + main + "'"
        if (type == 'month'):
            sql_str = "select city_name,`" + main + "` from city_month where date= '" + date + "'"
            sql = "select city_name,category from cluster_month where date= '" + date + "' and main_index='" + main + "'"
        if (type == 'quarter'):
            sql_str = "select city_name,`" + main + "` from city_quarter where date= '" + date[0] + "'"
            sql = "select city_name,category from cluster_quarter where date= '" + date[0] + "' and main_index='" + main + "'"
        req = db.get_conn(sql_str)
        req1 = db.get_conn(sql)
        dict_a = {}
        dict_b = {}
        for item in req:
            dict_a.update({item[0]: [item[0], item[1]]})
        for item in req1:
            dict_b.update({item[0]: item[1]})
        result_dict = {}
        for k, v in dict_a.items():
            for m, n in dict_b.items():
                if k == m:
                    result_dict[k] = dict_a[k]
                    result_dict[k].append(dict_b[k])
                    dict_a[k] = result_dict[k]
                    dict_b[k] = result_dict[k]
                else:
                    result_dict[k] = dict_a[k]
                    result_dict[m] = dict_b[m]
        list1 = list(result_dict.values())
        keys = ['city_name','main_index','category','rate']
        data = []
        for item in list1:
            t = item
            t.append(0)
            data.append(dict(zip(keys, t)))
    else:
        if (type == 'week'):
            date1 = datetime.datetime.strptime(date, '%Y-%m-%d').date() + datetime.timedelta(days=-7)
            sql = "select city_name,`" + main + "` from city_week where date= '" + str(date1) + "'"
            sql_str = "select city_name,`" + main + "` from city_week where date= '" + date + "'"
            sql1 = "select city_name,category from cluster_week where date= '" + date + "' and main_index='" + main + "'"
        if (type == 'month'):
            date1 = (datetime.datetime.strptime(date, '%Y-%m').date() - relativedelta(months=1)).strftime('%Y-%m')
            sql = "select city_name,`" + main + "` from city_month where date= '" + str(date1) + "'"
            sql_str = "select city_name,`" + main + "` from city_month where date= '" + date + "'"
            sql1 = "select city_name,category from cluster_month where date= '" + date + "' and main_index='" + main + "'"
        if (type == 'quarter'):
            date1 = (datetime.datetime.strptime(date[0], '%Y-%m').date() - relativedelta(months=3)).strftime('%Y-%m')
            sql = "select city_name,`" + main + "` from city_quarter where date= '" + str(date1) + "'"
            sql_str = "select city_name,`" + main + "` from city_quarter where date= '" + date[0] + "'"
            sql1 = "select city_name,category from cluster_quarter where date= '" + date[0] + "' and main_index='" + main + "'"
        req = db.get_conn(sql_str)
        re_req = db.get_conn(sql)
        req1 = db.get_conn(sql1)
        dict_a = {}
        dict_b = {}
        for item in req:
            dict_a.update({item[0]: [item[0], item[1]]})
        for item in req1:
            dict_b.update({item[0]: item[1]})
        result_dict = {}
        for k, v in dict_a.items():
            for m, n in dict_b.items():
                if k == m:
                    result_dict[k] = dict_a[k]
                    result_dict[k].append(dict_b[k])
                    dict_a[k] = result_dict[k]
                    dict_b[k] = result_dict[k]
                else:
                    result_dict[k] = dict_a[k]
                    result_dict[m] = dict_b[m]
        list1 = list(result_dict.values())
        data = []
        for i in range(361):
            index = req[i][1]
            index1 = re_req[i][1]
            rate = '%.2f%%' % (((index - index1) / index1) * 100)
            data.append({'city_name': req[i][0], 'main_index': req[i][1],'category':list1[i][2],'rate': rate})
    res = sort(data)[0:20]
    return jsonify(res)

def sort(data):
    """
    冒泡排序
    :param data:
    :return:
    """
    for i in range(len(data) - 1):
        for j in range(0, len(data) - i - 1):
            if data[j]['main_index'] < data[j + 1]['main_index']:
                t = data[j]
                data[j] = data[j + 1]
                data[j + 1] = t
    return data

# def send_cluster_main_index():
#     """
#     一段时间范围城市群主要指标平均值
#     :return:
#     """
#     mes = request.get_json()
#     startDate = mes['startDate']
#     endDate = mes['endDate']
#     type = mes['type']
#     main = mes['main']
#     if (type == 'week'):
#         sql_str = "select city_name,date,`"+main+"` from city_week where date between '" + startDate + "' and '" + endDate + "'"
#         sql = "select city_name,date,category from cluster_week where date between '" + startDate + "' and '" + endDate + "' and main_index = '" + main +"'"
#     if (type == 'month'):
#         sql_str = "select city_name,date,`" + main + "` from city_month where date between '" + startDate + "' and '" + endDate + "'"
#         sql = "select city_name,date,category from cluster_month where date between '" + startDate + "' and '" + endDate + "' and main_index = '" + main + "'"
#     if (type == 'quarter'):
#         sql_str = "select city_name,date,`" + main + "` from city_quarter where date between '" + startDate + "' and '" + endDate + "'"
#         sql = "select city_name,date,category from cluster_quarter where date between '" + startDate + "' and '" + endDate + "' and main_index = '" + main + "'"
#     req = db.get_conn(sql_str)
#     req1 = db.get_conn(sql)
#     dict_a = {}
#     dict_b = {}
#     for item in req:
#         dict_a.update({item[0] + item[1]: [item[0], item[1], item[2]]})
#     for item in req1:
#         dict_b.update({item[0] + item[1]: item[2]})
#     result_dict = {}
#     for k, v in dict_a.items():
#         for m, n in dict_b.items():
#             if k == m:
#                 result_dict[k] = dict_a[k]
#                 result_dict[k].append(dict_b[k])
#                 dict_a[k] = result_dict[k]
#                 dict_b[k] = result_dict[k]
#             else:
#                 result_dict[k] = dict_a[k]
#                 result_dict[m] = dict_b[m]
#     list1 = list(result_dict.values())
#     columns = ['city_name','date','main_index','category']
#     data = pd.DataFrame(columns=columns,data=list1).groupby('category')
#     number = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13]
#     if (type == 'week'):
#         sql1 = "select distinct date from city_week where date between '" + startDate + "' and '" + endDate + "'"
#     if (type == 'month'):
#         sql1 = "select distinct date from city_month where date between '" + startDate + "' and '" + endDate + "'"
#     if (type == 'quarter'):
#         sql1 = "select distinct date from city_quarter where date between '" + startDate + "' and '" + endDate + "'"
#     time = []
#     for s in db.get_conn(sql1):
#         time.append(s[0])
#     res = []
#     for item in number:
#         new_data = data.get_group(item).to_dict('record')
#         data1 = pd.DataFrame(columns=columns, data=new_data).groupby('date')
#         for item1 in time:
#             new_new_data = data1.get_group(item1).to_dict('record')
#             num = len(new_new_data)
#             sum = 0
#             for item2 in new_new_data:
#                 sum = sum + item2['main_index']
#             ave = round(sum / num, 2)
#             res.append({'category': item,'date': item1, 'main_index': ave})
#     return jsonify(res)
def send_cluster_main_index():
    mes = request.get_json()
    startDate = mes['startDate']
    endDate = mes['endDate']
    type = mes['type']
    main = mes['main']
    main_value = cityv.get_value(startDate, endDate, type, main)
    return jsonify(main_value)

def send_cluster_main_main():
    mes = request.get_json()
    startDate = mes['startDate']
    endDate = mes['endDate']
    type = mes['type']
    main = mes['main']
    main_value = citym.get_main(startDate, endDate, type, main)
    return jsonify(main_value)


def send_iaqi():
    mes = request.get_json()
    type = mes['type']
    date = mes['date']
    main = mes['main']
    print(mes)
    if (type == 'week'):
        sql_str = "select city_name,`RH(%%)`,`PSFC(Pa)`, `风速`, `风向`, `摄氏温度`,`AQI` from city_week where date= '"+date+"'"
        sql = "select city_name,`PM2.5(微克每立方米)`,`PM10(微克每立方米)`,`SO2(微克每立方米)`,`NO2(微克每立方米)`,`CO(毫克每立方米)`,`O3(微克每立方米)` from pollution_iaqi_week where date= '"+date+"'"
        sql1 = "select city_name,category from cluster_week where date= '" + date + "' and main_index='" + main + "'"
    if (type == 'month'):
        sql_str = "select city_name,`RH(%%)`,`PSFC(Pa)`, `风速`, `风向`, `摄氏温度`,`AQI` from city_month where date= '"+date+"'"
        sql = "select city_name,`PM2.5(微克每立方米)`,`PM10(微克每立方米)`,`SO2(微克每立方米)`,`NO2(微克每立方米)`,`CO(毫克每立方米)`,`O3(微克每立方米)` from pollution_iaqi_month where date= '" + date + "'"
        sql1 = "select city_name,category from cluster_month where date= '" + date + "' and main_index='" + main + "'"
    if (type == 'quarter'):
        sql_str = "select city_name,`RH(%%)`,`PSFC(Pa)`, `风速`, `风向`, `摄氏温度`,`AQI` from city_quarter where date= '"+date[0]+"'"
        sql = "select city_name,`PM2.5(微克每立方米)`,`PM10(微克每立方米)`,`SO2(微克每立方米)`,`NO2(微克每立方米)`,`CO(毫克每立方米)`,`O3(微克每立方米)` from pollution_iaqi_quarter where date= '" + date[0] + "'"
        sql1 = "select city_name,category from cluster_quarter where date= '" + date[0] + "' and main_index='" + main + "'"
    req = db.get_conn(sql)
    req1 = db.get_conn(sql1)
    req2 = db.get_conn(sql_str)
    dict_a = {}
    dict_b = {}
    dict_c = {}
    for item in req:
        dict_a.update({item[0]: [item[0], item[1], item[2], item[3], item[4], item[5], item[6]]})
    for item in req1:
        dict_b.update({item[0]: item[1]})
    for item in req2:
        dict_c.update({item[0]: [item[1], item[2], item[3], item[4], item[5],item[6]]})
    result_dict = {}
    for k, v in dict_a.items():
        for m, n in dict_c.items():
            for x, y in dict_b.items():
                if k == m and k == x:
                    result_dict[k] = dict_a[k]
                    for item in dict_c[k]:
                        result_dict[k].append(item)
                    result_dict[k].append(dict_b[k])
    list1 = list(result_dict.values())
    columns = ['city_name', 'PM2.5(微克每立方米)', 'PM10(微克每立方米)', 'SO2(微克每立方米)', 'NO2(微克每立方米)', 'CO(毫克每立方米)', 'O3(微克每立方米)', 'RH(%)', 'PSFC(Pa)', '风速', '风向', '摄氏温度','AQI','category']
    data = pd.DataFrame(columns=columns, data=list1).groupby('category')
    number = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13]
    pea = []
    vv = []
    for item in number:
        new_data = data.get_group(item).to_dict('record')
        citys = []
        indexs = []
        AQI = []
        for item1 in new_data:
            index = []
            AQI.append(item1['AQI'])
            citys.append(item1['city_name'])
            index.append(item1['PM2.5(微克每立方米)'])
            index.append(item1['PM10(微克每立方米)'])
            index.append(item1['SO2(微克每立方米)'])
            index.append(item1['NO2(微克每立方米)'])
            index.append(item1['CO(毫克每立方米)'])
            index.append(item1['O3(微克每立方米)'])
            indexs.append(index)
        # vv.append({'category':new_data[0]['category'],'data':getIaqi(citys,indexs,AQI)})
        pea1 = []
        pea2 = []
        for i in range(1, 7):
            for j in range(i+1, 7):
                pea1.append(get_pearsonr(new_data, columns[i], columns[j]))
        for i in range(1, 7):
            for j in range(i+1, 12):
                if (j > 6):
                    pea2.append(get_pearsonr(new_data, columns[i], columns[j]))
        pea.append({'category':new_data[0]['category'],
                    'data': [{'type':'pollution','data':pea1},{'type':'atmosphere','data':pea2}],
                    'city': getIaqi(citys,indexs,AQI)})

    return jsonify(pea)

def get_pearsonr(data,x,y):
    a = []
    b = []
    for item in data:
        a.append(item[x])
        b.append(item[y])
    # return {x+'_'+y:pearsonr(a, b)[0],'category':data[0]['category']}
    return {x + '_' + y: pearsonr(a, b)[0]}
def getIaqi(citys,data,AQI):
    """
    将拿到的数据归一化，并计算出坐标
    :return:
    """
    transfer = MinMaxScaler(feature_range=[0, 1])
    data_new = transfer.fit_transform(data)
    M = 0.5
    city_v = []
    for j in range(len(data_new)):
        item = data_new[j]
        k = []
        for i in range(6):
            k.append(M * (math.cos(math.pi / 2 * item[i]) + 1))
        v1 = item[0] * math.cos(math.pi / 3) * k[0] - item[1] * math.cos(math.pi / 3) * k[1] - item[2] * math.cos(
            math.pi / 3) * k[2] + item[3] * math.cos(math.pi / 3) * k[3] + item[4] * k[4] - item[5] * k[5]
        v2 = -item[0] * math.sin(math.pi / 3) * k[0] - item[1] * math.sin(math.pi / 3) * k[1] + item[2] * math.sin(
            math.pi / 3) * k[2] + item[3] * math.sin(math.pi / 3) * k[3]
        city_v.append({'x':v1,'y':v2,'AQI':AQI[j],'city_name':citys[j]})
    return city_v